DECLARE
	res NUMBER;
BEGIN
	SELECT COUNT(1) INTO res FROM ALL_TABLES WHERE UPPER(TABLE_NAME) = 'TRF_CLIENT';
	
	IF res <> 0 THEN
		EXECUTE IMMEDIATE 'DROP TABLE TRF_ACCOUNT';
		EXECUTE IMMEDIATE 'DROP TABLE TRF_CLIENT';
		EXECUTE IMMEDIATE 'DROP TABLE TRF_TARIFF';
		EXECUTE IMMEDIATE 'DROP TABLE TRF_FREE_ACCOUNTS';
		EXECUTE IMMEDIATE 'DROP SEQUENCE ACC_ID';
		EXECUTE IMMEDIATE 'DROP SEQUENCE CLT_ID';
	END IF;							
END;	
/
 
CREATE SEQUENCE ACC_ID INCREMENT BY 1 START WITH 9999990;
CREATE SEQUENCE CLT_ID MINVALUE 0 START WITH 0 INCREMENT BY 1;
SELECT ACC_ID.NEXTVAL,CLT_ID.NEXTVAL FROM DUAL;
 
 CREATE TABLE TRF_TARIFF
 (
 TARIFF_ID NUMBER PRIMARY KEY,
 TARIFF_NAME VARCHAR2(32) UNIQUE,
 OUT_NET NUMBER, --price of outgoing call to subscriber of other network
 IN_NET NUMBER, --price of outgoing call in client network
 IN_ROAMING NUMBER, --price markup in roaming for incoming call
 OUT_ROAMING NUMBER, --price markup in roaming for outgoing call
 SMS NUMBER
 );
 
 CREATE TABLE TRF_CLIENT
 (
 CLIENT_ID NUMBER PRIMARY KEY,
 CLIENT_NAME VARCHAR2(64),
 PASSPORT_NUMBER NUMBER
 );
 
 CREATE TABLE TRF_ACCOUNT
 (
 ACCOUNT_ID NUMBER PRIMARY KEY,
 CLIENT_ID NUMBER, FOREIGN KEY (CLIENT_ID) REFERENCES TRF_CLIENT(CLIENT_ID),
 TARIFF_ID NUMBER, FOREIGN KEY (TARIFF_ID) REFERENCES TRF_TARIFF(TARIFF_ID), 
 BALANCE NUMBER,
 STATUS CHAR(1) 
 );

 ALTER TABLE TRF_ACCOUNT ADD CHECK(STATUS IN ('-','+'));
 
 CREATE TABLE TRF_FREE_ACCOUNTS
(
 	ACCOUNT_ID INTEGER PRIMARY KEY
);
